package org.jflame.commons.excel;

import org.jflame.commons.excel.handler.ArrayRowWriter;
import org.jflame.commons.excel.handler.EntityRowWriter;
import org.jflame.commons.excel.handler.IExcelRowWriter;
import org.jflame.commons.model.PageBean;
import org.jflame.commons.util.CollectionHelper;
import org.jflame.commons.util.DateHelper;
import org.jflame.commons.util.IOHelper;
import org.jflame.commons.util.StringHelper;

import java.io.Closeable;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.function.Consumer;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

/**
 * <p>
 * 本类基于Apache POI封装提供Excel文件(只支持office2007+格式)的创建,数据导出功能.
 * <ol>
 * <li>在实体类的字段属性或对应的get方法上使用{@linkplain ExcelCoumn}注解来指定要导出的列名和格式等
 * </ol>
 * 示例1:
 * 
 * <pre>
 * 
 * List&lt;Pet&gt; pets = new ArrayList&lt;&gt;();
 * // 填充数据集合...
 * try (ExcelCreator xlsCreator = new ExcelCreator(); FileOutPutStream out = new FileOutPutStream("xxxx.xlsx")) {
 *     xlsCreator.fillEntityData(pets);
 *     xlsCreator.write(out);
 * }
 * 
 * </pre>
 * 
 * 示例2:
 * 
 * <pre>
 * ExcelCreator.export(pets, "d://xxxx.xlsx");
 * </pre>
 * 
 * @see ExcelColumn
 * @author zyc
 */
public class ExcelCreator implements Closeable {

    private static final int DEFAULT_ROWACCESS_SIZE = 100;

    private SXSSFWorkbook workbook;
    private CellStyle titleStyle;// 标题行单元格样式
    private CellStyle cellStyle;// 默认全局单元格样式
    private Map<Integer,Integer> rowIndexMap = new HashMap<>();// 记录每个sheet的当前行索引
    private int startColIndex = 0;// 开始填充的列索引
    private Sheet currentSheet;
    private Integer currentSheetIndex;
    private boolean noTitle = false;
    // 属性分组模式,true表示严格只选择指定名分组的属性,false宽松选择指定分组和未设备任何分组的属性
    private boolean groupStrict = false;

    /**
     * 构造函数. 默认内存缓冲行数100
     */
    public ExcelCreator() {
        this(DEFAULT_ROWACCESS_SIZE);
    }

    /**
     * 构造函数.
     * 
     * @param rowAccessWindowSize 内存缓冲行数,超过数据行将写入磁盘.默认{@linkplain #DEFAULT_ROWACCESS_SIZE 100}行
     */
    public ExcelCreator(int rowAccessWindowSize) {
        workbook = new SXSSFWorkbook(rowAccessWindowSize);
        currentSheet = workbook.createSheet();
        currentSheetIndex = 0;
    }

    /**
     * 将实体数据集合填充到指定的工作表,不指定分组
     * 
     * @param dataList 实体数据集合
     * @exception ExcelAccessException
     */
    public <T> void fillEntityData(final Collection<T> dataList) {
        fillEntityData(dataList, null);
    }

    /**
     * 将实体数据集合填充到指定的工作表
     * 
     * @param dataList 实体数据集合
     * @param group 分组，null时导出所有{@code @ExcelColumn}标注的的属性
     * @exception ExcelAccessException
     */
    @SuppressWarnings("unchecked")
    public <T> void fillEntityData(final Collection<T> dataList, final String group) {
        if (CollectionHelper.isEmpty(dataList)) {
            return;
        }
        Iterator<T> it = dataList.iterator();
        Class<T> dataClass = null;
        while (it.hasNext()) {
            T t = it.next();
            if (t != null) {
                dataClass = (Class<T>) t.getClass();
                break;
            }
        }
        EntityRowWriter<T> rowHandler = createEntityRowWriter(dataClass, group);
        if (!noTitle) {
            createTitleRow(currentSheet, rowHandler.getColumnPropertys());
        }
        setDefaultColumnStyle(currentSheet, rowHandler.getColumnPropertys());
        fillData(dataList, rowHandler);
    }

    public <T> void fillData(final Iterable<T> dataList, IExcelRowWriter<T> rowWriter) {
        if (currentSheet == null) {
            selectSheet(0);
        }

        Row row = null;
        for (T rowData : dataList) {
            row = currentSheet.createRow(getAndMoveRowIndex());
            rowWriter.fillRow(rowData, row, startColIndex, cellStyle);
        }
    }

    /**
     * 将Object[]数据集合填充到工作表.
     * 
     * @param titles 标题行数组,可以为空
     * @param data List&lt;Object[]&gt;, 集合数组元素长度应相同
     */
    public void fillArrayData(final String[] titles, final List<Object[]> data) {
        if (currentSheet == null) {
            selectSheet(0);
        }
        if (ArrayUtils.isNotEmpty(titles)) {
            createTitleRow(currentSheet, titles);
        }
        if (CollectionHelper.isEmpty(data)) {
            return;
        }
        List<ExcelColumnProperty> properties = ExcelColumnProperty.resolveColumnProperty(titles, data);
        setDefaultColumnStyle(currentSheet, properties, data);
        fillData(data, new ArrayRowWriter(properties));
    }

    /**
     * 分批次获取实体数据导出到excel.
     * <p>
     * 当数据量较大时,一次性拉取所有数据到内存容易导致OOM发生,该方法使用分批拉取写入的方式避免
     * 
     * @param maxRow 导出数据总大小
     * @param batchSize 一次获取数据行数
     * @param group 分组，null时导出所有{@code @ExcelColumn}标注的的属性
     * @param batchDataFetcher 获取每个批次数据的函数，传入分页对象PageBean,分页数据设置到对象pageData属性
     */
    @SuppressWarnings({ "unchecked" })
    public <T> void batchFillData(final int maxRow, final int batchSize, final String group,
            final Consumer<PageBean<T>> batchDataFetcher) {
        PageBean<T> page = new PageBean<>(1, batchSize);
        page.setMaxRowCount(maxRow);
        EntityRowWriter<T> rowWriter = null;
        do {
            batchDataFetcher.accept(page);
            if (CollectionHelper.isEmpty(page.getPageData())) {
                break;
            }
            if (rowWriter == null) {
                rowWriter = createEntityRowWriter((Class<T>) page.getPageData()
                        .get(0)
                        .getClass(), group);
                // 注解填充自动生成标题行
                if (!noTitle) {
                    createTitleRow(currentSheet, rowWriter.getColumnPropertys());
                }
                setDefaultColumnStyle(currentSheet, rowWriter.getColumnPropertys());
            }
            fillData(page.getPageData(), rowWriter);
            page.setPageNo(page.getPageNo() + 1);
        } while (page.getPageNo() <= page.getMaxPage());

    }

    /**
     * 合并单元格
     * 
     * @param startRow 起始行
     * @param endRow 结束行
     * @param startCol 起始列
     * @param endCol 结束列
     */
    public void mergedCell(int startRow, int endRow, int startCol, int endCol) {
        CellRangeAddress region = new CellRangeAddress(startRow, endRow, startCol, endCol);
        currentSheet.addMergedRegion(region);
    }

    /**
     * 设置 POI XSSFSheet 单元格提示或选择框
     *
     * @param sheet 表单
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public void setPromptOrValidation(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow,
            int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = textlist.length > 0 ? helper.createExplicitListConstraint(textlist)
                : helper.createCustomConstraint("DD1");
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        if (StringHelper.isNotEmpty(promptContent)) {
            // 如果设置了提示信息则鼠标放上去提示
            dataValidation.createPromptBox("", promptContent);
            dataValidation.setShowPromptBox(true);
        }
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }

    /**
     * 创建一个工作表.
     * 
     * @return 创建完成的工作表对象Sheet
     */
    public Sheet createSheet() {
        return workbook.createSheet();
    }

    /**
     * 创建一个工作表,并指定工作表名.
     * 
     * @param sheetName 工作表名
     * @return 创建完成的工作表对象Sheet
     */
    public Sheet createSheet(String sheetName) {
        return workbook.createSheet(sheetName);
    }

    /**
     * 按名称返回excel sheet.
     * 
     * @param sheetName excel sheet name
     * @return Sheet
     */
    public Sheet getSheet(String sheetName) {
        return workbook.getSheet(sheetName);
    }

    /**
     * 按索引返回excel sheet.
     * 
     * @param sheetIndex excel sheet index
     * @return Sheet
     */
    public Sheet getSheet(int sheetIndex) {
        return workbook.getSheetAt(sheetIndex);
    }

    /**
     * 选择要操作的sheet
     * 
     * @param sheetIndex sheet索引
     */
    public ExcelCreator selectSheet(int sheetIndex) {
        currentSheet = workbook.getSheetAt(sheetIndex);
        currentSheetIndex = sheetIndex;
        return this;
    }

    /**
     * 选择要操作的sheet
     * 
     * @param sheetName sheet名称
     */
    public ExcelCreator selectSheet(String sheetName) {
        currentSheet = workbook.getSheet(sheetName);
        currentSheetIndex = workbook.getSheetIndex(currentSheet);
        return this;
    }

    /**
     * 返回当前excel sheet.
     * 
     * @return Workbook
     */
    public Workbook getWorkbook() {
        return workbook;
    }

    public void write(Path xlsPath) throws ExcelAccessException {
        try (OutputStream output = Files.newOutputStream(xlsPath)) {
            workbook.write(output);
            output.flush();
        } catch (IOException e) {
            throw new ExcelAccessException("excel写入异常", e);
        }
    }

    /**
     * 写入工作薄到一个输入流.
     * 
     * @param output 输出流
     * @throws ExcelAccessException
     */
    public void write(OutputStream output) throws ExcelAccessException {
        if (output != null) {
            try {
                workbook.write(output);
                output.flush();
            } catch (IOException e) {
                throw new ExcelAccessException("excel写入异常", e);
            }
        }
    }

    @Override
    public void close() {
        if (workbook != null) {
            try {
                workbook.dispose();
                workbook.close();
            } catch (IOException e) {
                workbook = null;
            }
        }
    }

    public CellStyle getCellStyle() {
        return cellStyle;
    }

    /**
     * 设置单元格默认样式
     * 
     * @param cellStyle
     */
    public void setCellStyle(CellStyle cellStyle) {
        this.cellStyle = cellStyle;
    }

    public Sheet getCurrentSheet() {
        return currentSheet;
    }

    /**
     * 设置是否需要标题栏
     * 
     * @param noNeedTitle true=无标题栏
     */
    public ExcelCreator setNoTitle(boolean noNeedTitle) {
        this.noTitle = noNeedTitle;
        return this;
    }

    /**
     * 取得sheet当前行索引并下移一行
     * 
     * @param sheet
     * @return
     */
    private int getAndMoveRowIndex() {
        Integer index = rowIndexMap.get(currentSheetIndex);
        if (index == null) {
            rowIndexMap.put(currentSheetIndex, 1);
            return 0;
        } else {
            rowIndexMap.put(currentSheetIndex, index + 1);
            return index;
        }
    }

    /**
     * 返回当前行索引(注:列表写完一行后索引下移)
     * 
     * @return
     */
    public Integer getRowIndex() {
        return rowIndexMap.get(currentSheetIndex);
    }

    public int getStartColIndex() {
        return startColIndex;
    }

    public void setStartColIndex(int startColIndex) {
        this.startColIndex = startColIndex;
    }

    /**
     * 设置标题行单元格样式.
     * 
     * @param titleStyle 样式
     */
    public void setTitleStyle(CellStyle titleStyle) {
        this.titleStyle = titleStyle;
    }

    /**
     * 设置当前sheet的写索引
     * 
     * @param rowIndex
     */
    public void moveRowIndex(int rowIndex) {
        rowIndexMap.put(currentSheetIndex, rowIndex);
    }

    /**
     * 在第一个工作表上创建标题行.
     * 
     * @param titleNames 标题列的名称
     */
    public void createTitleRow(String[] titleNames) {
        createTitleRow(getSheet(0), titleNames);
    }

    /**
     * 指定的工作表上创建标题行.
     * 
     * @param sheet excel sheet
     * @param titleNames 标题列的名称
     */
    public void createTitleRow(Sheet sheet, String[] titleNames) {
        Row row = sheet.createRow(getAndMoveRowIndex());
        if (titleStyle == null) {
            titleStyle = initDefaultTitleRowStyle();
        }
        Cell cell;
        for (int i = 0; i < titleNames.length; i++) {
            cell = row.createCell(startColIndex + i);
            cell.setCellValue(titleNames[i]);
        }
    }

    private void createTitleRow(Sheet sheet, List<ExcelColumnProperty> columns) {
        Row row = sheet.createRow(getAndMoveRowIndex());
        if (titleStyle == null) {
            titleStyle = initDefaultTitleRowStyle();
        }
        Cell cell;
        int size = columns.size();
        ExcelColumnProperty columnProp;
        for (int i = 0; i < size; i++) {
            cell = row.createCell(startColIndex + i);
            cell.setCellStyle(titleStyle);
            columnProp = columns.get(i);
            cell.setCellValue(columnProp.getName());
            // sheet.setColumnWidth(cell.getColumnIndex(), columnProp.getWidth());
        }
    }

    private CellStyle initDefaultTitleRowStyle() {
        CellStyle defaultTitleStyle = workbook.createCellStyle();
        defaultTitleStyle.setAlignment(HorizontalAlignment.CENTER);
        org.apache.poi.ss.usermodel.Font titleCellFont = workbook.createFont();// 字体
        titleCellFont.setBold(true);
        defaultTitleStyle.setFont(titleCellFont);
        return defaultTitleStyle;
    }

    private final int defaultColumnWidth = 15 * 256;

    private void setDefaultColumnStyle(final Sheet sheet, final List<ExcelColumnProperty> columns) {
        int size = columns.size(),columnIndex;
        ExcelColumnProperty columnProp;

        for (int i = 0; i < size; i++) {
            columnIndex = startColIndex + i;
            columnProp = columns.get(i);
            sheet.setColumnWidth(columnIndex, columnProp.getWidth() > 0 ? columnProp.getWidth() : defaultColumnWidth);
            // 保持excel里数据类型,设置poi dataFormat
            if (columnProp.getColumnType() == 0 && columnProp.getWriteConverter() == null) {
                Class<?> propertyType = columnProp.getPropertyDescriptor()
                        .getPropertyType();
                String defaultFmt = null;
                if (StringHelper.isNotEmpty(columnProp.getFmt())) {
                    defaultFmt = columnProp.getFmt();
                } else {
                    // 给日期类型设置默认格式
                    if (propertyType == LocalDateTime.class || propertyType == java.sql.Timestamp.class
                            || propertyType == Date.class || propertyType == Calendar.class) {
                        defaultFmt = DateHelper.YYYY_MM_DD_HH_mm_ss;
                    } else if (propertyType == LocalDate.class || propertyType == java.sql.Date.class) {
                        defaultFmt = DateHelper.YYYY_MM_DD;
                    } else if (propertyType == LocalTime.class || propertyType == java.sql.Time.class) {
                        defaultFmt = DateHelper.HH_mm_ss;
                    }
                }
                if (defaultFmt != null) {
                    short fmtIdx = sheet.getWorkbook()
                            .createDataFormat()
                            .getFormat(defaultFmt);
                    if (columnProp.getColumnStyle() == null) {
                        CellStyle style = sheet.getWorkbook()
                                .createCellStyle();
                        if (cellStyle != null) {
                            style.cloneStyleFrom(cellStyle);
                        }
                        style.setDataFormat(fmtIdx);
                        columnProp.setColumnStyle(style);
                    } else {
                        columnProp.getColumnStyle()
                                .setDataFormat(fmtIdx);
                    }
                }
            }

            /*这里列设置无效,需要单元格设置
             * if (columnProp.getColumnStyle() != null) {
                sheet.setDefaultColumnStyle(columnIndex, columnProp.getColumnStyle());
            }*/

        }
    }

    private void setDefaultColumnStyle(final Sheet sheet, final List<ExcelColumnProperty> columns,
            final List<Object[]> data) {
        Class<?> propertyType = null;
        Object currentValue;
        int tmpIndex;
        tmpIndex = 0;
        currentValue = null;
        int columnIndex;
        ExcelColumnProperty columnProp;

        for (int i = 0; i < columns.size(); i++) {
            columnIndex = startColIndex + i;
            columnProp = columns.get(i);
            // 找第一个不是null的值
            do {
                Object[] rowData = data.get(tmpIndex++);
                if (rowData != null && rowData[i] != null) {
                    currentValue = rowData[i];
                    break;
                }
            } while (tmpIndex < data.size());
            if (currentValue == null) {
                continue;
            }
            sheet.setColumnWidth(columnIndex, defaultColumnWidth);
            propertyType = currentValue.getClass();
            String defaultFmt = null;
            // 给日期类型设置默认格式
            if (propertyType == LocalDateTime.class || propertyType == java.sql.Timestamp.class
                    || propertyType == Date.class || propertyType == Calendar.class) {
                defaultFmt = DateHelper.YYYY_MM_DD_HH_mm_ss;
                sheet.setColumnWidth(startColIndex + i, 24 * 256);
            } else if (propertyType == LocalDate.class || propertyType == java.sql.Date.class) {
                defaultFmt = DateHelper.YYYY_MM_DD;
            } else if (propertyType == LocalTime.class || propertyType == java.sql.Time.class) {
                defaultFmt = DateHelper.HH_mm_ss;
            } else if (propertyType == Long.class || propertyType == long.class) {
                defaultFmt = "0";
            }
            if (defaultFmt != null) {
                short fmtIdx = sheet.getWorkbook()
                        .createDataFormat()
                        .getFormat(defaultFmt);

                CellStyle style = sheet.getWorkbook()
                        .createCellStyle();
                if (cellStyle != null) {
                    style.cloneStyleFrom(cellStyle);
                }
                style.setDataFormat(fmtIdx);
                columnProp.setColumnStyle(style);
            }
        }
    }

    public boolean isGroupStrict() {
        return groupStrict;
    }

    public void setGroupStrict(boolean groupStrict) {
        this.groupStrict = groupStrict;
    }

    protected <T> EntityRowWriter<T> createEntityRowWriter(final Class<T> dataClass, final String group) {
        List<ExcelColumnProperty> columnPropertys = ExcelColumnProperty.resolveColumnProperty(dataClass, true,
                Optional.ofNullable(group), groupStrict, workbook);
        if (CollectionHelper.isEmpty(columnPropertys)) {
            throw new ExcelAccessException(
                    "No field with @ExcelColumn annotation was found in the " + dataClass.getName());
        }
        return new EntityRowWriter<>(columnPropertys);
    }

    public static <T> void export(final Collection<T> data, final OutputStream out, boolean isCloseOutStream) {
        export(data, null, out, isCloseOutStream);
    }

    /**
     * 导出实体类数据到单表的便捷方法.
     * 
     * @param data 要导出数据集
     * @param dataGroup 分组
     * @param out 文件输出流
     * @param isCloseOutStream 写入完成后是否关闭输出流
     * @throws ExcelAccessException
     */
    public static <T> void export(final Collection<T> data, final String dataGroup, final OutputStream out,
            boolean isCloseOutStream) throws ExcelAccessException {
        ExcelCreator creator = null;
        try {
            creator = new ExcelCreator();
            creator.fillEntityData(data, dataGroup);
            creator.write(out);
        } catch (ExcelAccessException e) {
            throw e;
        } finally {
            if (creator != null) {
                creator.close();
            }
            if (isCloseOutStream) {
                IOHelper.closeQuietly(out);
            }
        }
    }

    /**
     * 导出实体类数据到单表的便捷方法.自动关闭输出流
     * 
     * @param data 要导出数据集
     * @param excelFile 文件
     * @throws ExcelAccessException
     */
    public static <T> void export(final Collection<T> data, final String excelFile) throws ExcelAccessException {
        ExcelCreator.export(data, null, excelFile);
    }

    public static <T> void export(final Collection<T> data, final String dataGroup, final String excelFile)
            throws ExcelAccessException {
        OutputStream out;
        try {
            out = Files.newOutputStream(Paths.get(excelFile));
        } catch (IOException e) {
            throw new ExcelAccessException(e);
        }
        ExcelCreator.export(data, dataGroup, out, true);
    }

}
